﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL.Entities;
using System.Data;
using System.Data.SqlClient;

namespace DAL.EntitiesDAL
{
   public  class ban_DAL:BaseDAL,UTL.IBaseDAL  
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            string sql = @" SELECT * FROM BAN";
            return ExecuteQuery(sql);
        }

        public DataTable Select_tten()
        {
            string sql = @" SELECT maban as id , tenban as ten FROM BAN";
            return ExecuteQuery(sql);
        }

        public DataTable Select_load()
        {
            string sql = @" SELECT maloai as id, tenloai as ten FROM loaimon";
            return ExecuteQuery(sql);
        }

        public DataTable Select_KV(string kv)
        {
            var sql = @" SELECT * FROM BAN where makv = '{0}'";
            sql = string.Format(sql, kv);
            return ExecuteQuery(sql);
        }

        public DataTable load_loaimon(string loai)
        {
            var sql = @" SELECT ma FROM thucdon where maloai = '{0}'";
            sql = string.Format(sql, loai);
            return ExecuteQuery(sql);
        }

        public DataTable Select_phieu(string ten)
        {
            string sql = @" SELECT a.maban, tenban, tinhtien FROM PHIEU a, ban b where a.maban=b.maban and a.maban ='{0}'";
            return ExecuteQuery(sql);
        }

        public DataTable load()
        {
            string sql = @" SELECT * FROM PHIEU";
            return ExecuteQuery(sql);
        }

        public DataTable thucdon_loaimon(string tenban)
        {
            try
            {
                var sql = @" 
                            select mamon, tenmon, DVT, dongia
                            from thucdon
                            where maloai ='{0}'
                            
                            ";
                sql = string.Format(sql, tenban);
                return ExecuteQuery(sql);
            }
            catch
            {
                return null;
            }
        }

        public DataTable hien_thi(string tenban)
        {
            var sql = @" 
                            select mamon, tenmon, DVT, dongia
                            from thucdon
                            where maloai ='{0}'
                            
                            ";
            sql = string.Format(sql, tenban);
            return ExecuteQuery(sql);
        }

        public DataTable hthi(string tenban)
        {
            var sql = @" 
                            select tenmon, soluong, b.dongia, giamgia, a.maban, a.maphieu, a.tinhtien
                            from phieu a, chitietphieu b, thucdon c, ban d
                            where a.maphieu = b.maphieu
                            and a.maban = d.maban
                            and b.mamon = c.mamon
                            and a.maban ='{0}'
                            
                            ";
            sql = string.Format(sql, tenban);
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM BAN WHERE MABAN = '{0}'";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (Ban )obj;
            string sql = @"INSERT INTO BAN(MABAN, TENBAN,MAKV) VALUES('{0}',N'{1}', N'{2}') ";
            sql = string.Format(sql, o.MABAN, o.TENBAN, o.MAKV);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (Ban)obj;
            string sql = @"SET dateformat dmy;UPDATE BAN SET TENBAN=N'{1}',MAKV=N'{2}' WHERE MABAN='{0}'";
            sql = string.Format(sql, o.MABAN, o.TENBAN, o.MAKV);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable khuvuc()
        {
            string sql = @" SELECT MAKV AS id, TENKV AS ten FROM KHUVUC";
            return ExecuteQuery(sql);
        }

        public DataTable Select_max()
        {
            var sql = @"SELECT MAX(maban) as id FROM ban";
            return ExecuteQuery(sql);
        }

        public DataTable BAN()
        {
            var sql = @" select a.maban as id, a.tenban as ten from ban a where a.maban not in (select b.maban from phieu b)";
            return ExecuteQuery(sql);
        }

        public DataTable BANok()
        {
            var sql = @" select maban from phieu  where tinhtien = 0";
            return ExecuteQuery(sql);
        }
    }
}
